Nested Row Queries

This lesson discusses nested queries that return a set of rows.

We'll cover the following

Nested Row Queries#

In this lesson we’ll study nested queries that return rows, allowing the outer query to match on multiple different column values. Furthermore, so far, we have used nested queries only with the WHERE clause, but now we’ll also use them with the FROM clause.

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/32lesson.sh and wait for the MySQL prompt to start-up.

Terminal 1
Terminal

Click to Connect...

  1. Let’s say we want to find the list of all the actors whose latest update to any of their online accounts was on the day of their birthday. The date of birth for each actor is in the Actors table and the LastUpdatedOn column is in the DigitalAssets table. We can extract the birthday month and day using the MONTH() and DAY() functions on the DoB column and match them with the corresponding extracted values from the LastUpdatedOn column. Finally, we’ll also need to match the actor IDs in the two tables. The inner join query to get the results is as follows:

    SELECT FirstName
    FROM Actors
    INNER JOIN DigitalAssets
    ON Id=ActorId 
    AND MONTH(DoB) = MONTH(LastUpdatedOn) 
    AND DAY(DoB) = DAY(LastUpdatedOn);

Instead of the inner join we can also use a nested query. We’ll return three columns from the inner query, the day and month of the last update and the actor ID. The outer query will match on these three columns using the IN clause.

SELECT FirstName

FROM Actors 

WHERE (Id, MONTH(DoB), DAY(DoB))

IN ( SELECT ActorId, MONTH(LastUpdatedOn), DAY(LastUpdatedOn)
     FROM DigitalAssets);

The inner query returns a temporary result set of several rows with three columns. The outer query lists columns from the Actors table that should be matched against the columns from the result set of the inner query and the match takes place in the order of the listing of columns. The syntax allows us to match multiple columns per row for several rows. The first name column of the matching rows of the inner query’s result set are then returned as the result of the overall query.

  1. To demonstrate using a nested query with the FROM clause, we’ll move onto a slightly harder query to answer. Say you are asked to find out which of her online accounts Kim Kardashian most recently updated. Let’s think about it for a minute: the two pieces of information we need are present in the two tables: Actors (name) and DigitalAssets (last update timestamp). First, let’s understand how we can find the latest updated account for an actor. If we know the ActorID, we can use the following query to list all the online accounts belonging to that actor along with their latest update times.

    SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets;

The above query also retrieves us the actor IDs. If we could determine Kardashian’s actor ID from the output of the above query, we could use that in a WHERE clause and answer the original question, but we don’t. We’ll need to join the result of the above query with the actor table based on actor IDs to know which rows from the DigitalAssets table belong to Kardashian. So far, we have:

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors 

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 
            FROM DigitalAssets) AS tbl 

ON ActorId = Id;

Note that we give an alias of tbl to the result set of the inner query. When the result of an inner query is used as a derived table, MySQL requires us to provide an alias for the table. This is a syntax requirement. If we skip aliasing the result set of the inner query, we’ll not be able to use it in the join clause. In order to narrow down the rows for Kardashian we’ll need to add a WHERE clause with the condition FirstName=“Kim”. Now we’ll have all the digital accounts belonging to Kardashian as follows:

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors 

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 
            FROM DigitalAssets) AS tbl 

ON ActorId = Id

WHERE FirstName = "Kim";

The last piece is to order the rows by LastUpdatedOn to get the latest updated online account for Kardashian.

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors 

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 
            FROM DigitalAssets) AS tbl 

ON ActorId = Id

WHERE FirstName = "Kim"

ORDER BY LastUpdatedOn DESC LIMIT 1;

The astute reader would realize that in the FROM clause we could have just as well used the DigitalAssets table instead of plugging in a nested query. Sure, we could, but the intent here is to demonstrate how nested queries can be used with the FROM clause, so in that sense, it is a slightly contrived example.

Nested Column Queries
EXISTS Operator
Mark as Completed
Report an Issue